package gov.pbc.nn.core.bdf.role;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.jdbc.core.RowMapper;

import com.bstek.bdf2.core.model.Group;
import com.bstek.bdf2.core.model.RoleMember;
import com.bstek.bdf2.core.orm.ParseResult;
import com.bstek.bdf2.core.orm.hibernate.ISessionCallback;
import com.bstek.bdf2.core.service.IDeptService;
import com.bstek.bdf2.core.service.IPositionService;
import com.bstek.bdf2.core.service.IUserService;
import com.bstek.bdf2.core.view.role.RoleMaintain;
import com.bstek.dorado.annotation.DataProvider;
import com.bstek.dorado.annotation.Expose;
import com.bstek.dorado.data.entity.EntityUtils;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Page;

import gov.pbc.nn.core.bdf.entity.User;
import gov.pbc.nn.core.dao.PbcnnCoreHibernateDao;
import gov.pbc.nn.core.dao.PbcnnCoreJdbcDao;

public class RoleMemberMaintain extends RoleMaintain {
	private IDeptService deptService;

	private IPositionService positionService;

	private IUserService userService;

	@Resource(name = PbcnnCoreJdbcDao.BEAN_ID)
	private PbcnnCoreJdbcDao jdbcDao;
	@Resource(name=PbcnnCoreHibernateDao.BEAN_ID)
	private PbcnnCoreHibernateDao hibernateDao;

	@DataProvider
	public void loadMembers(final Page<RoleMember> page, Criteria criteria,final Map<String, Object> parameter) throws Exception {
		final Map<String, Object> valueMap = new LinkedHashMap<String, Object>();
		valueMap.put("roleId", parameter.get("roleId"));
		String hql = "from " + RoleMember.class.getName() + " rm where rm.roleId=:roleId and";
		String type = (String) parameter.get("type");
		if (type.equals("user")) {
			String sql = "SELECT\n" +
					"	rm.*, t.*,t.id_ as deptId,t.DEPT_NAME_ as deptName\n" +
					"FROM\n" +
					"	bdf2_role_member rm\n" +
					"INNER JOIN (\n" +
					"	SELECT\n" +
					"		u.*, d.ID_,\n" +
					"		d.NAME_ as DEPT_NAME_\n" +
					"	FROM\n" +
					"		t_user u\n" +
					"	INNER JOIN bdf2_user_dept ud ON ud.USERNAME_ = u.USERNAME_\n" +
					"	INNER JOIN t_dept d ON ud.DEPT_ID_ = d.ID_\n" +
					") t ON rm.USERNAME_ = t.username_\n" +
					"WHERE\n" +
					"	rm.ROLE_ID_ =:roleId ";
			String countSql = "SELECT\n" +
					"	count(rm.id_)\n" +
					"FROM\n" +
					"	bdf2_role_member rm\n" +
					"INNER JOIN (\n" +
					"	SELECT\n" +
					"		u.*, d.ID_,\n" +
					"		d.NAME_ as DEPT_NAME_\n" +
					"	FROM\n" +
					"		t_user u\n" +
					"	INNER JOIN bdf2_user_dept ud ON ud.USERNAME_ = u.USERNAME_\n" +
					"	INNER JOIN t_dept d ON ud.DEPT_ID_ = d.ID_\n" +
					") t ON rm.USERNAME_ = t.username_\n" +
					"WHERE\n" +
					"	rm.ROLE_ID_ =:roleId ";
			ParseResult result = this.jdbcDao.parseCriteria(criteria, true, null);
			if(result!=null){
				sql += " and "+result.getAssemblySql();
				countSql += " and "+result.getAssemblySql();
				valueMap.putAll(result.getValueMap());
			}
			final String querySql = sql;
			final String countQuerySql = countSql;
			this.hibernateDao.doInHibernateSession(new ISessionCallback<Void>(){

				@Override
				public Void doInSession(Session session) {
					SQLQuery query = session.createSQLQuery(querySql);
					query.addEntity("rm",RoleMember.class);
					query.addEntity("t",User.class);
					query.addScalar("deptId", StandardBasicTypes.STRING);
					query.addScalar("deptName", StandardBasicTypes.STRING);
					query.setProperties(valueMap);
					query.setFirstResult((page.getPageNo()-1)*page.getPageSize());
					query.setMaxResults(page.getPageSize());
					List<Object[]> list = query.list();
					
					List<RoleMember> rmList = new ArrayList<RoleMember>();
					for(Object[] array:list){
						RoleMember rm = (RoleMember) array[0];
						try {
							User user = EntityUtils.toEntity(array[1]);
							EntityUtils.setValue(user,"deptName", array[3]);
							rm.setUser(user);
						} catch (Exception e) {
							e.printStackTrace();
						}
						rmList.add(rm);
					}
					page.setEntities(rmList);
					
					query = session.createSQLQuery(countQuerySql);
					valueMap.put("roleId", parameter.get("roleId"));
					query.setProperties(valueMap);
					Number n = (Number) query.uniqueResult();
					page.setEntityCount(n.intValue());
					return null;
				}
				
			});
		} else {
			if (type.equals("dept")) {
				hql += " rm.deptId is not null";
			} else if (type.equals("position")) {
				hql += " rm.positionId is not null";
			} else if (type.equals("group")) {
				hql += " rm.group is not null";
			}
			String countHql = "select count(*) " + hql;
			hql += " order by rm.createDate desc";
			valueMap.put("roleId", parameter.get("roleId"));
			this.pagingQuery(page, hql, countHql, valueMap);
			for (RoleMember member : page.getEntities()) {
//				if (StringUtils.isNotEmpty(member.getUsername())) {
//					member.setUser((IUser) userService.loadUserByUsername(member.getUsername()));
//				}
				if (StringUtils.isNotEmpty(member.getDeptId())) {
					member.setDept(deptService.loadDeptById(member.getDeptId()));
				}
				if (StringUtils.isNotEmpty(member.getPositionId())) {
					member.setPosition(positionService.loadPositionById(member.getPositionId()));
				}
			}
		}
	}

	@Expose
	public void changeGranted(Collection<Map<String, Object>> members) throws Exception {
		Session session = this.getSessionFactory().openSession();
		try {
			for (Map<String, Object> map : members) {
				RoleMember rm = (RoleMember) session.load(RoleMember.class, (String) map.get("memberId"));
				rm.setGranted((Boolean) map.get("granted"));
				session.update(rm);
			}
		} finally {
			session.flush();
			session.close();
		}
	}

	@Expose
	public void deleteRoleMember(String memberId) throws Exception {
		RoleMember rm = new RoleMember();
		rm.setId(memberId);
		Session session = this.getSessionFactory().openSession();
		try {
			session.delete(rm);
		} finally {
			session.flush();
			session.close();
		}
	}

	@Expose
	public String insertRoleMember(Collection<String> ids, String type, String roleId) throws Exception {
		Session session = this.getSessionFactory().openSession();
		try {
			String error = null;
			List<RoleMember> members = new ArrayList<RoleMember>();
			for (String id : ids) {
				RoleMember roleMember = new RoleMember();
				roleMember.setId(UUID.randomUUID().toString());
				roleMember.setRoleId(roleId);
				roleMember.setGranted(true);
				roleMember.setCreateDate(new Date());
				int count = 0;
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("roleId", roleId);
				if (type.equals("user")) {
					map.put("username", id);
					count = this.queryForInt("select count(*) from " + RoleMember.class.getName()
							+ " rm where rm.roleId=:roleId and rm.username=:username", map);
					roleMember.setUsername(id);
				} else if (type.equals("dept")) {
					map.put("deptId", id);
					count = this.queryForInt("select count(*) from " + RoleMember.class.getName()
							+ " rm where rm.roleId=:roleId and rm.deptId=:deptId", map);
					roleMember.setDeptId(id);
				} else if (type.equals("position")) {
					map.put("positionId", id);
					count = this.queryForInt("select count(*) from " + RoleMember.class.getName()
							+ " rm where rm.roleId=:roleId and rm.positionId=:positionId", map);
					roleMember.setPositionId(id);
				} else if (type.equals("group")) {
					map.put("groupId", id);
					count = this.queryForInt("select count(*) from " + RoleMember.class.getName()
							+ " rm where rm.roleId=:roleId and rm.group.id=:groupId", map);
					Group group = new Group();
					group.setId(id);
					roleMember.setGroup(group);
				}
				if (count > 0) {
					error = id;
					break;
				} else {
					members.add(roleMember);
				}
			}
			if (error == null) {
				for (RoleMember roleMember : members) {
					session.save(roleMember);
				}
			}
			return error;
		} finally {
			session.flush();
			session.close();
		}
	}

	public IDeptService getDeptService() {
		return deptService;
	}

	public void setDeptService(IDeptService deptService) {
		this.deptService = deptService;
	}

	public IPositionService getPositionService() {
		return positionService;
	}

	public void setPositionService(IPositionService positionService) {
		this.positionService = positionService;
	}

	public IUserService getUserService() {
		return userService;
	}

	public void setUserService(IUserService userService) {
		this.userService = userService;
	}
}
